package blackberryAppBus;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Hashtable;

import blackberryApp.BBApp_cjbBBApp;
import blackberryApp.BBApp_cjbPOL;
import blackberryApp.BBApp_cjbLETTER;
import blackberryApp.BBApp_cjbREG_MAIN;
import blackberryAppCom.BBApp_Common;
import blackberryAppCom.BBApp_Exception;

public class BBApp_Business {
	
	private String lStrSchema=BBApp_Common.fnGetProperty("DB_SCHEMA");	
	private String lStrSmSchema=BBApp_Common.fnGetProperty("DB_SCHEMA_SM");
	
	/**
	 * fnGetDataClaim
	 */
	public Hashtable fnGetDataClaim(String lStrPolicy, String lStrRegNo, String lStrRecNo) throws BBApp_Exception {
		StringBuffer lSbrqry	      	= new StringBuffer();
		Connection lCnFetch		      	= null;
		PreparedStatement lPStmtFetch 	= null;
		ResultSet lRsFetch		      	= null;
		Hashtable lHstDataClaim 		= new Hashtable();
		BBApp_cjbREG_MAIN lObjRegMain 	= null;
		Hashtable lHstDataReg 			= new Hashtable();
		
		try {
			lCnFetch = new BBApp_Common().OpenConnection(1);
			if (lCnFetch == null) {
				throw new BBApp_Exception("NoDataBase Connection","1500");
			}
			
			lSbrqry.delete(0, lSbrqry.length());
			lSbrqry.append("select * from ( ");
			lSbrqry.append("  select zz.*, rownum row_num from ( ");
			lSbrqry.append("    select b.PARTICIPANT_KLAIM_RUN, a.KLAIM_NO, d.NAME, a.REGISTER_DATE, a.POLICY_NO ");
			lSbrqry.append("    , f.GROUP_PROG_NAME, b.DATE_OF_LOSS, b.LOCATION, (g.DIAGNOSIS_KODE||' '|| g.DIAGNOSIS_DESC) diagnosa ");
			lSbrqry.append("    , a.ACCEPTED_NO, a.ACCEPTED_DATE, ");
			lSbrqry.append("    (select  sum(h.CLAIM_ACCEPTED) from sm.t_claim_program h "); 
			lSbrqry.append("     where h.REGISTER_NO=b.register_no ");
			lSbrqry.append("     and h.PARTICIPANT_KLAIM_RUN=b.PARTICIPANT_KLAIM_RUN) TOT_ACCEPTED_CLAIM, ");
			lSbrqry.append("    (select  sum(i.CLAIM_PROPOSED) from sm.t_claim_program i ");
			lSbrqry.append("     where i.REGISTER_NO=b.register_no ");
			lSbrqry.append("     and i.PARTICIPANT_KLAIM_RUN=b.PARTICIPANT_KLAIM_RUN) TOT_CLAIM_PROPOSED, "); 
			lSbrqry.append("    (select tgl_lunas from sm.t_request h, sm.t_request_data i ");
			lSbrqry.append("     where h.REQUEST_NO=i.REQUEST_NO "); 
			lSbrqry.append("     and i.POLICY_NO=a.POLICY_NO "); 
			lSbrqry.append("     and i.ACCEPTED_NO=a.ACCEPTED_NO "); 
			lSbrqry.append("     and i.REGISTER_NO=a.REGISTER_NO ) TGL_LUNAS, ");  
			lSbrqry.append("    CASE a.sts_aso ");                                                          
			lSbrqry.append("      WHEN '0' THEN CASE NVL (a.accepted_no, 'null') ");              
			lSbrqry.append("                      WHEN 'null' THEN 'Klaim sedang diproses' ");                   
			lSbrqry.append("                      ELSE CASE NVL (a.request_no, 'null') ");                           
			lSbrqry.append("                             WHEN 'null' THEN 'Klaim sedang diproses' ");                   
			lSbrqry.append("                             ELSE CASE b.tot_accepted_claim ");  
			lSbrqry.append("                                    WHEN 0 THEN 'Klaim ditolak' ");                           
			lSbrqry.append("                                    ELSE CASE z.sts_lunas ");                     
			lSbrqry.append("                                           WHEN '0' THEN 'Sedang Proses Pembayaran' ");                
			lSbrqry.append("                                           WHEN '1' THEN 'Klaim sudah dibayar' ");                     
			lSbrqry.append("                                         END ");                                                  
			lSbrqry.append("                                  END ");                                                  
			lSbrqry.append("                           END ");                                                  
			lSbrqry.append("                    END ");
			lSbrqry.append("      WHEN '1' THEN CASE NVL (a.accepted_no, 'null') ");              
			lSbrqry.append("                      WHEN 'null' THEN 'Klaim sedang diproses' ");                   
			lSbrqry.append("                      ELSE CASE NVL (a.request_no, 'null') ");        
			lSbrqry.append("                             WHEN 'null' THEN 'Klaim sedang diproses' ");                   
			lSbrqry.append("                             ELSE CASE b.tot_accepted_claim ");  
			lSbrqry.append("                                    WHEN 0 THEN 'Klaim ditolak' ");                           
			lSbrqry.append("                                    ELSE CASE NVL (TO_CHAR (z.print_date), 'null' ) ");                                   
			lSbrqry.append("                                           WHEN 'null' THEN 'Klaim sedang diproses' ");                   
			lSbrqry.append("                                           ELSE 'Klaim sudah dibayar' ");                          
			lSbrqry.append("                                         END ");                                                  
			lSbrqry.append("                                  END ");                                                  
			lSbrqry.append("                           END ");                                                  
			lSbrqry.append("                    END ");                                                  
			lSbrqry.append("    END status_klaim ");
			
			lSbrqry.append("    , (select tgl_bayar from sm.t_request h, sm.t_request_data i ");
			lSbrqry.append("    		 where h.REQUEST_NO=i.REQUEST_NO ");  
			lSbrqry.append("    	     and i.POLICY_NO=a.POLICY_NO ");  
			lSbrqry.append("    	     and i.ACCEPTED_NO=a.ACCEPTED_NO ");  
			lSbrqry.append("    	     and i.REGISTER_NO=a.REGISTER_NO ) TGL_BAYAR ");
			lSbrqry.append("    , decode(b.sts_provider, 1, 'Provider', 'Non Provider') sts_provider ");
			lSbrqry.append("    , (select ww.cdn_total ");
			lSbrqry.append("          from sm.t_claim_cdnote qq, sm.t_claim_cdnote_detail ww ");
			lSbrqry.append("          where 1=1 ");
			lSbrqry.append("          and qq.cdnote_id = ww.cdnote_id ");
			lSbrqry.append("          and qq.register_no = ww.register_no ");
			lSbrqry.append("          and a.register_no = qq.register_no(+) ");
			lSbrqry.append("          and b.participant_klaim_run = ww.participant_klaim_run (+) ");
			lSbrqry.append("      ) total_ekses ");
			lSbrqry.append("    , (select qq.lunas_date ");
			lSbrqry.append("          from sm.t_claim_cdnote qq, sm.t_claim_cdnote_detail ww ");
			lSbrqry.append("          where 1=1 ");
			lSbrqry.append("          and qq.cdnote_id = ww.cdnote_id ");
			lSbrqry.append("          and qq.register_no = ww.register_no ");
			lSbrqry.append("          and a.register_no = qq.register_no(+) ");
			lSbrqry.append("          and b.participant_klaim_run = ww.participant_klaim_run (+) ");
			lSbrqry.append("      ) tgl_lunas_ekses ");
			
			lSbrqry.append("    from SM.T_CLAIM a, SM.T_CLAIM_PARTICIPANT b  , sm.t_sppa c ");
			lSbrqry.append("    , sm.t_participant d, sm.m_program e, sm.M_GROUP_PROGRAM f ");
			lSbrqry.append("    , sm.m_diagnosis g, sm.t_request z ");
			lSbrqry.append("    where 1=1 "); 
			lSbrqry.append("    and c.SPPA_NO=d.SPPA_NO "); 
			lSbrqry.append("    and a.POLICY_NO=c.POLICY_NO "); 
			lSbrqry.append("    and b.PARTICIPANT_ID=d.PARTICIPANT_ID "); 
			lSbrqry.append("    and b.REGISTER_NO=a.REGISTER_NO "); 
			lSbrqry.append("    and a.POLICY_NO='"+lStrPolicy+"' "); 
			lSbrqry.append("    and e.PROGRAM_ID=b.PROGRAM_ID "); 
			lSbrqry.append("    and e.GROUP_PROG_ID=f.GROUP_PROG_ID "); 
			lSbrqry.append("    and b.DIAGNOSIS_ID=g.DIAGNOSIS_ID "); 
			lSbrqry.append("    and upper(d.REG_NO)=upper('"+lStrRegNo+"') ");
			lSbrqry.append("    and a.request_no = z.request_no (+) ");
			lSbrqry.append("    order by a.REGISTER_DATE desc "); 
			lSbrqry.append("  ) zz ");
			lSbrqry.append(") ");			
			
			BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnGetDataClaim) : ","Query : " + lSbrqry.toString());
			
			lPStmtFetch = lCnFetch.prepareStatement(lSbrqry.toString());
			lRsFetch 	= lPStmtFetch.executeQuery();
			
			int j = 1;
			while(lRsFetch.next()) {
				lObjRegMain = new BBApp_cjbREG_MAIN();
				lObjRegMain.setPROGRAM_NAME(BBApp_Common.fnGetValue(lRsFetch.getString("PARTICIPANT_KLAIM_RUN")));
				lObjRegMain.setCLAIM_NO(BBApp_Common.fnGetValue(lRsFetch.getString("KLAIM_NO")));
				lObjRegMain.setREG_DATE(BBApp_Common.fnGetDateDDMMMYYYY(lRsFetch.getString("REGISTER_DATE")));
				lObjRegMain.setPOL_NO(BBApp_Common.fnGetValue(lRsFetch.getString("POLICY_NO")));
				lObjRegMain.setRESERVE_TYPE(BBApp_Common.fnGetValue(lRsFetch.getString("GROUP_PROG_NAME")));
				lObjRegMain.setADMIT_DATE(BBApp_Common.fnGetDateDDMMMYYYY(lRsFetch.getString("DATE_OF_LOSS")));
				lObjRegMain.setHOSP_CODE(BBApp_Common.fnGetValue(lRsFetch.getString("LOCATION")));
				lObjRegMain.setDIAGNOSA(BBApp_Common.fnGetValue(lRsFetch.getString("diagnosa")));
				lObjRegMain.setACCEPT_NO(BBApp_Common.fnGetValue(lRsFetch.getString("ACCEPTED_NO")));
				lObjRegMain.setAPPROVE_DATE(BBApp_Common.fnGetDateDDMMMYYYY(lRsFetch.getString("ACCEPTED_DATE")));
				lObjRegMain.setTOTAL_APPROVE(BBApp_Common.fnFormatNumberInd(lRsFetch.getString("TOT_ACCEPTED_CLAIM")));
				lObjRegMain.setTOTAL_AMT(BBApp_Common.fnFormatNumberInd(lRsFetch.getString("TOT_CLAIM_PROPOSED")));
				lObjRegMain.setDISCHRG_DATE(BBApp_Common.fnGetDateDDMMMYYYY(lRsFetch.getString("TGL_LUNAS")));
				lObjRegMain.setCLM_STAT(BBApp_Common.fnGetValue(lRsFetch.getString("status_klaim")));
				lObjRegMain.setTGL_BAYAR(BBApp_Common.fnGetDateDDMMMYYYY(lRsFetch.getString("TGL_BAYAR")));
				lObjRegMain.setSTS_PROVIDER(BBApp_Common.fnGetValue(lRsFetch.getString("sts_provider")));
				lObjRegMain.setTOT_EXCESS(BBApp_Common.fnFormatNumberInd(lRsFetch.getString("total_ekses")));
				lObjRegMain.setTGL_LUNAS_EKSES(BBApp_Common.fnGetDateDDMMMYYYY(lRsFetch.getString("tgl_lunas_ekses")));
				lHstDataReg.put(BBApp_Common.fnGetValue(lRsFetch.getString("row_num")), lObjRegMain);
				j++;	
			}
			
			if (lHstDataReg.size() > 0) {
//				BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnGetDataClaim) : ","Result Size : " + lHstDataReg.size());
//				BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnGetDataClaim) : ","Result Size : " + String.valueOf(j));
				
				BBApp_cjbREG_MAIN lObjRegResult = new BBApp_cjbREG_MAIN();
				BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnGetDataClaim) : ","lHstDataReg : "+lHstDataReg.size());
				BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnGetDataClaim) : ","lStrRecNo : "+lStrRecNo);
				if ((lStrRecNo.trim().length() == 0) || (lStrRecNo.equalsIgnoreCase("null"))) {
					lStrRecNo = "1";
				}
				lObjRegResult = (BBApp_cjbREG_MAIN) lHstDataReg.get(lStrRecNo);
				
				Hashtable htResult = new Hashtable();
				htResult.put("1", lObjRegResult);
				
				lHstDataClaim.put("Register", htResult);
				lHstDataClaim.put("MaxNo", String.valueOf(lHstDataReg.size()));
				lHstDataClaim.put("RecNo", lStrRecNo);
			}
			
		}
		catch(Exception ex) {
			BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnGetDataClaim) : ","Fetch Exception : "+ex);
			throw new BBApp_Exception("General Exception","2000");
		}
		finally {
			try {
				if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
				if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
				if (lCnFetch!=null) {lCnFetch.close(); lCnFetch=null;}
			}
			catch(Exception e) {
				BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnGetDataClaim) : ","Close Exception : "+e);
			}
		}
		
		return lHstDataClaim;
	}
	
//	/**
//	 * 
//	 * @param lStrPolicy
//	 * @param lStrRegNo
//	 * @param lStrType
//	 * @param lStrTypeSurat
//	 * @return
//	 * @throws BBApp_Exception
//	 */
//	public Hashtable fnGetDataClaimClaiment(String lStrPolicy, String lStrRegNo, String lStrType, String lStrTypeSurat) throws BBApp_Exception
//	{
//		StringBuffer lSbrqry	      = new StringBuffer();
//		Connection lCnFetch		      = null;
//		ResultSet lRsFetch		      = null;
//		PreparedStatement lPStmtFetch = null;	
//		
//		Hashtable lHstDataClaim = new Hashtable();
//		try
//		{
//			BBApp_cjbLETTER lObjCLHL_Letter; 
//			
//			lCnFetch = new BBApp_Common().OpenConnection(1);
//			if(lCnFetch == null)
//			{ //if connection is not established
//					
//				throw new BBApp_Exception("NoDataBase Connection","1500");
//			}
//			
//			lSbrqry.delete(0, lSbrqry.length());
//			lSbrqry.append("select b.PARTICIPANT_KLAIM_RUN,a.KLAIM_NO, d.NAME, a.REGISTER_DATE, a.POLICY_NO, f.GROUP_PROG_NAME, b.DATE_OF_LOSS, b.LOCATION,(g.DIAGNOSIS_KODE||' '|| g.DIAGNOSIS_DESC) diagnosa, a.ACCEPTED_NO, a.ACCEPTED_DATE, "); 
//			lSbrqry.append("(select  sum(h.CLAIM_ACCEPTED) from sm.t_claim_program h where h.REGISTER_NO=b.register_no and h.PARTICIPANT_KLAIM_RUN=b.PARTICIPANT_KLAIM_RUN) TOT_ACCEPTED_CLAIM,");
//			lSbrqry.append("(select  sum(i.CLAIM_PROPOSED) from sm.t_claim_program i where i.REGISTER_NO=b.register_no and i.PARTICIPANT_KLAIM_RUN=b.PARTICIPANT_KLAIM_RUN) TOT_CLAIM_PROPOSED, ");
//			lSbrqry.append("(select tgl_lunas from sm.t_request h, sm.t_request_data i where h.REQUEST_NO=i.REQUEST_NO and i.POLICY_NO=a.POLICY_NO and i.ACCEPTED_NO=a.ACCEPTED_NO and i.REGISTER_NO=a.REGISTER_NO ) TGL_LUNAS ");
//			lSbrqry.append("from SM.T_CLAIM a, SM.T_CLAIM_PARTICIPANT b, sm.t_sppa c, sm.t_participant d, sm.m_program e, sm.M_GROUP_PROGRAM f, sm.m_diagnosis g ");
//			lSbrqry.append("where 1=1 ");
//			lSbrqry.append("and c.SPPA_NO=d.SPPA_NO ");
//			lSbrqry.append("and a.POLICY_NO=c.POLICY_NO ");
//			lSbrqry.append("and b.PARTICIPANT_ID=d.PARTICIPANT_ID ");
//			lSbrqry.append("and b.REGISTER_NO=a.REGISTER_NO ");
//			lSbrqry.append("and a.POLICY_NO='"+lStrPolicy+"' ");
//			lSbrqry.append("and e.PROGRAM_ID=b.PROGRAM_ID ");
//			lSbrqry.append("and e.GROUP_PROG_ID=f.GROUP_PROG_ID ");
//			lSbrqry.append("and b.DIAGNOSIS_ID=g.DIAGNOSIS_ID ");
//			lSbrqry.append("and d.REG_NO='"+lStrRegNo+"'");
//			lSbrqry.append("and a.ACCEPTED_NO is not null ");
//			//lSbrqry.append("order by a.ACCEPTED_DATE");
//			
//			if(!lStrType.equals("") || lStrType.length() > 0)
//			{
//				lSbrqry.append("and a.STS_PROGRAM="+lStrType+" order by a.ACCEPTED_DATE ");		
//			}
//			else
//			{
//				lSbrqry.append("order by a.REGISTER_DATE desc ");	
//			}
//					
//			BBApp_Common.fnPrint("Query untuk sql : "+lSbrqry.toString());
//			
//			lPStmtFetch = lCnFetch.prepareStatement(lSbrqry.toString());
//			lRsFetch 	= lPStmtFetch.executeQuery();
//			
//			BBApp_cjbREG_MAIN lObjRegMain = null;
//			
//			int j = 1;
//			Hashtable lHstDataReg = new Hashtable();
//			
//			//Untuk sementara nomor surat, perlu diupdate
//			while(lRsFetch.next())
//			{
//				lObjRegMain = new BBApp_cjbREG_MAIN();
//				lObjRegMain.setPROGRAM_NAME(BBApp_Common.fnGetValue(lRsFetch.getString("PARTICIPANT_KLAIM_RUN")));
//				lObjRegMain.setCLAIM_NO(BBApp_Common.fnGetValue(lRsFetch.getString("KLAIM_NO")));
//				lObjRegMain.setREG_DATE(BBApp_Common.fnGetValue(lRsFetch.getString("REGISTER_DATE")));
//				lObjRegMain.setPOL_NO(BBApp_Common.fnGetValue(lRsFetch.getString("POLICY_NO")));
//				lObjRegMain.setRESERVE_TYPE(BBApp_Common.fnGetValue(lRsFetch.getString("GROUP_PROG_NAME")));
//				lObjRegMain.setADMIT_DATE(BBApp_Common.fnGetValue(lRsFetch.getString("DATE_OF_LOSS")));
//				lObjRegMain.setHOSP_CODE(BBApp_Common.fnGetValue(lRsFetch.getString("LOCATION")));
//				lObjRegMain.setDIAGNOSA(BBApp_Common.fnGetValue(lRsFetch.getString("diagnosa")));
//				
//				lObjRegMain.setACCEPT_NO(BBApp_Common.fnGetValue(lRsFetch.getString("ACCEPTED_NO")));
//				lObjRegMain.setAPPROVE_DATE(BBApp_Common.fnGetValue(lRsFetch.getString("ACCEPTED_DATE")));
//				lObjRegMain.setTOTAL_APPROVE(BBApp_Common.fnGetValue(lRsFetch.getString("TOT_ACCEPTED_CLAIM")));
//				
//				lObjRegMain.setTOTAL_AMT(BBApp_Common.fnGetValue(lRsFetch.getString("TOT_CLAIM_PROPOSED")));
//				lObjRegMain.setDISCHRG_DATE(BBApp_Common.fnGetValue(lRsFetch.getString("TGL_LUNAS")));
//				lHstDataReg.put(""+j, lObjRegMain);
//				
//				j++;	
//			}
//			
//			lHstDataClaim.put("Register", lHstDataReg);
//				
//			//Dijamin
//			
//			Hashtable lHstLetter = new Hashtable();
//			int i = 1;
//			
//			if(lStrTypeSurat.equals("0")|| lStrTypeSurat.equals(""))
//			{
//				lSbrqry.delete(0, lSbrqry.length());
//				if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
//				if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}	
//				lSbrqry.append("select a.POLICY_NO, a.REG_NO, a.LETTER_DATE, a.WARR_LETTER_NO, 'Dijamin'  status, a.STS_AKSEP");
//				lSbrqry.append(",a.CHECK_IN, (select d.HOSPITAL_ID||' '||d.HOSPITAL_NAME from DB2INST2.M_HOSPITAL_PROVIDER_HED d where d.HOSPITAL_ID=a.HOSPITAL_ID) Hospital, "); 
//				lSbrqry.append("(select c.DIAGNOSIS_ID||' '||c.DIAGNOSIS_DESC from sm.m_diagnosis c where c.DIAGNOSIS_ID=a.DIAGNOSIS_ID) kode_diagnosa, b.SIGNATURE_NAME, '' Ditolak ");
//				lSbrqry.append("from sm.t_warr_letter a, sm.m_signature b where 1=1 ");
//				lSbrqry.append("and a.LUS_ID=b.SIGNATURE_ID ");
//				lSbrqry.append("and a.POLICY_NO='"+lStrPolicy+"' ");
//				lSbrqry.append("and a.REG_NO='"+lStrRegNo+"'");
//				//select a.POLICY_NO, a.REG_NO, a.LETTER_DATE, a.WARR_LETTER_NO, decode(a.STS_AKSEP, 1, 'Dijamin', 'Ditolak') status, a.STS_AKSEP
//				//,a.CHECK_IN, a.HOSPITAL_ID, (select c.DIAGNOSIS_ID||' '||c.DIAGNOSIS_DESC from sm.m_diagnosis c where c.DIAGNOSIS_ID=a.DIAGNOSIS_ID) kode_diagnosa, a.LUS_ID, b.SIGNATURE_NAME  from sm.t_warr_letter a, sm.m_signature b 
//				//where
//				//1=1
//				//and a.LUS_ID=b.SIGNATURE_ID
//				//and a.POLICY_NO='40117200900008' 
//				
//				BBApp_Common.fnPrint("Query untuk tes : "+lSbrqry.toString());
//				
//				lPStmtFetch = lCnFetch.prepareStatement(lSbrqry.toString());
//				lRsFetch 	= lPStmtFetch.executeQuery();
//				
//				
//				
//				
//				while(lRsFetch.next())
//				{
//					//testing
//					lObjCLHL_Letter = new BBApp_cjbLETTER(); 	
//					
//					lObjCLHL_Letter.setCLHL_LETTER_DATE(BBApp_Common.fnGetValue(lRsFetch.getString("LETTER_DATE")));
//					lObjCLHL_Letter.setCLHL_STS_LETTER(BBApp_Common.fnGetValue(lRsFetch.getString("status")));
//					lObjCLHL_Letter.setCLHL_REJECT_REPRO_NO(BBApp_Common.fnGetValue(lRsFetch.getString("WARR_LETTER_NO")));
//					lObjCLHL_Letter.setCLHL_RECEIPT_DATE(BBApp_Common.fnGetValue(lRsFetch.getString("CHECK_IN")));
//					lObjCLHL_Letter.setCLHL_HOSP_NAME(BBApp_Common.fnGetValue(lRsFetch.getString("Hospital")));
//					lObjCLHL_Letter.setCLHL_ICD_DESCRIPTION(BBApp_Common.fnGetValue(lRsFetch.getString("kode_diagnosa")));
//					lObjCLHL_Letter.setCLHL_REJECT_REASON(BBApp_Common.fnGetValue(lRsFetch.getString("Ditolak")));
//					lObjCLHL_Letter.setCLHL_SIGNATURE_NAME(BBApp_Common.fnGetValue(lRsFetch.getString("SIGNATURE_NAME")));
//					lObjCLHL_Letter.setCLHL_STS_REJECT("");
//					lHstLetter.put(""+i, lObjCLHL_Letter);
//					i++;		
//				}
//			}
//			
//			//Tolakkan
//			
//			if(!(lStrTypeSurat.equals("0")) || lStrTypeSurat.equals(""))
//			{
//				lSbrqry.delete(0, lSbrqry.length());
//				if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
//				if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
//				
//				lSbrqry.append("SELECT A.POLICY_NO, B.REG_NO, A.REJECT_REPRO_NO, A.LETTER_DATE, '' CHECK_IN, DECODE(A.STS_REJECT, '1', 'Ditolak', '2', 'Proses Ulang') status, ");
//				lSbrqry.append("(SELECT C.HOSPITAL_ID||' '||C.HOSPITAL_NAME FROM SM.M_HOSPITAL_PROVIDER C WHERE C.HOSPITAL_ID=A.HOSPITAL_ID) Hospital, B.REASON, ");
//				lSbrqry.append("(SELECT D.SIGNATURE_NAME FROM SM.M_SIGNATURE D WHERE D.SIGNATURE_ID=A.LUS_ID) AKSEP, A.STS_REJECT, B.DATE_OF_LOSS ");
//				lSbrqry.append("FROM SM.T_REJECT_REPRO_LETTER A, SM.T_REJECT_REPRO_PARTICIPANT B ");
//				lSbrqry.append("WHERE ");
//				lSbrqry.append("A.POLICY_NO='"+lStrPolicy+"' ");
//				lSbrqry.append("AND A.REJECT_REPRO_ID=B.REJECT_REPRO_ID ");
//				lSbrqry.append("AND B.REG_NO='"+lStrRegNo+"' ");
//				
//				if(!lStrTypeSurat.equals(""))
//				{
//					lSbrqry.append("AND STS_REJECT='"+lStrTypeSurat+"'");
//				}
//				BBApp_Common.fnPrint("Query untuk tes 123 : "+lSbrqry.toString());
//				
//				lPStmtFetch = lCnFetch.prepareStatement(lSbrqry.toString());
//				lRsFetch 	= lPStmtFetch.executeQuery();
//				
//				
//				
//				while(lRsFetch.next())
//				{
//					
//					
//					lObjCLHL_Letter = new BBApp_cjbLETTER();
//					
//					lObjCLHL_Letter.setCLHL_LETTER_DATE(BBApp_Common.fnGetValue(lRsFetch.getString("LETTER_DATE")));
//					lObjCLHL_Letter.setCLHL_STS_LETTER(BBApp_Common.fnGetValue(lRsFetch.getString("status")));
//					lObjCLHL_Letter.setCLHL_REJECT_REPRO_NO(BBApp_Common.fnGetValue(lRsFetch.getString("REJECT_REPRO_NO")));
//					lObjCLHL_Letter.setCLHL_RECEIPT_DATE(BBApp_Common.fnGetValue(lRsFetch.getString("CHECK_IN")));
//					lObjCLHL_Letter.setCLHL_HOSP_NAME(BBApp_Common.fnGetValue(lRsFetch.getString("Hospital")));
//					//lObjCLHL_Letter.setCLHL_ICD_DESCRIPTION(BBApp_Common.fnGetValue(lRsFetch.getString("kode_diagnosa")));
//					lObjCLHL_Letter.setCLHL_REJECT_REASON(BBApp_Common.fnGetValue(lRsFetch.getString("REASON")));
//					lObjCLHL_Letter.setCLHL_SIGNATURE_NAME(BBApp_Common.fnGetValue(lRsFetch.getString("AKSEP")));
//					lObjCLHL_Letter.setCLHL_STS_REJECT(BBApp_Common.fnGetValue(lRsFetch.getString("STS_REJECT")));
//					//DATE_OF_LOSS 
//					lObjCLHL_Letter.setCLHL_RECEIPT_DATE(BBApp_Common.fnGetValue(lRsFetch.getString("DATE_OF_LOSS")));
//					lHstLetter.put(""+i, lObjCLHL_Letter);	
//					i++;	
//				}
//			
//			//lHstDataClaim.put("Letter", lHstLetter);
//			}
//			
//			lHstDataClaim.put("Letter", lHstLetter);
//			BBApp_Common.fnPrint("Data Masuk Letter Surat Tolakkan : "+lHstLetter.size());
//			
//			
//			
//		}
//		catch(Exception ex)
//		{
//			BBApp_Common.fnWriteLog("CLHS_ejbREGISTRATION", "CLHS_ejbREGISTRATION(fnGetDataClaimClaiment) : ","Fetch Exception : "+ex);
//			throw new BBApp_Exception("General Exception","2000");
//		}
//		finally
//		{
//			try
//			{
//				if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
//				if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
//				if (lCnFetch!=null) {lCnFetch.close(); lCnFetch=null;}
//			}
//			catch(Exception e) 
//			{
//				BBApp_Common.fnWriteLog("CLHS_ejbREGISTRATION1", "CLHS_ejbREGISTRATION1(fnGetDataClaimClaiment) : ","Close Exception : "+e);
//			}
//		}
//		
//		return lHstDataClaim;
//				
//	}
	
	/**
	 * 
	 * @return
	 * @throws BBApp_Exception
	 */
	public Hashtable fnViewPolis(String strNoPolis, String strNoReg) throws BBApp_Exception
	{
		Connection lCnFetch		      = null;	
		PreparedStatement lPStmtFetch = null;		
		ResultSet lRsFetch		      = null;
		StringBuffer lSBquery 		  = new StringBuffer();
		
		Hashtable hTable = new Hashtable();
		try
		{
			lCnFetch = new BBApp_Common().OpenConnection(1);
			if(lCnFetch == null)
			{ //if connection is not established
					
				throw new BBApp_Exception("NoDataBase Connection","1500");
			}	
				
			lSBquery.append("select a.policy_no, b.reg_no, c.program_id, c.plan_id ");
			lSBquery.append(", d.program_name, e.plan_name, f.group_prog_name2 ");
			lSBquery.append("from sm.t_sppa a, sm.t_participant b, sm.t_plan c ");
			lSBquery.append(", sm.m_program d, sm.m_plan e, sm.m_group_program f ");
			lSBquery.append("where 1=1 ");
			lSBquery.append("and a.sppa_no = b.sppa_no ");
			lSBquery.append("and a.sppa_no = c.sppa_no ");
			lSBquery.append("and b.participant_id = c.participant_id ");
			lSBquery.append("and c.program_id = d.program_id ");
			lSBquery.append("and c.program_id = e.program_id ");
			lSBquery.append("and c.plan_id = e.plan_id ");
			lSBquery.append("and d.group_prog_id = f.group_prog_id ");
			lSBquery.append("and a.policy_no = '"+strNoPolis+"' ");
			lSBquery.append("and b.reg_no = '"+strNoReg+"' ");			
			
			BBApp_Common.fnPrint("[fnRetrieveReservetype] : "+lSBquery.toString());
			
			lPStmtFetch = lCnFetch.prepareStatement(lSBquery.toString());
			lRsFetch = lPStmtFetch.executeQuery();
			int i = 1;
			 
			while(lRsFetch.next())
			{
				String[] st = new String[8];
				
				st[0] = lRsFetch.getString("GROUP_PROG_ID");
				st[1] = lRsFetch.getString("GROUP_PROG_NAME"); 
				st[2] = "";
				st[3] = "";
				st[4] = "";
				st[5] = ""; 
				st[6] = "";
				st[7] = "";
				hTable.put(""+i, st);
				
				i++;		
			}
		}
		
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
				if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
				if (lCnFetch!=null) {lCnFetch.close(); lCnFetch=null;}
			}
			catch(Exception e) 
			{
				BBApp_Common.fnWriteLog("CLHS_ejbREGISTRATION1", "CLHS_ejbREGISTRATION1(fnRetrieveClaims) : ","Close Exception : "+e);
			}		
		}
		return hTable;
		
		
				
	}
	
	/**
	 * 
	 * @param lStrProgId
	 * @param lStrPlanId
	 * @return
	 * @throws BBApp_Exception
	 */
	public Hashtable fnGetBenefitLim(String lStrProgId, String lStrPlanId) throws BBApp_Exception
	{
		Connection lCnFetch		      = null;	
		PreparedStatement lPStmtFetch = null;		
		ResultSet lRsFetch		      = null;
		StringBuffer lSBquery 		  = new StringBuffer();
		Hashtable lHstResult = new Hashtable();
		
		try
		{
			lCnFetch = new BBApp_Common().OpenConnection(1);
			if(lCnFetch == null)
			{ //if connection is not established
					
				throw new BBApp_Exception("NoDataBase Connection","1500");
			}
			
			/*
			select b.BENEFIT_NAME,a.LIMIT  from
			sm.m_plan_benefit a, m_benefit b 
			where
			a.BENEFIT_ID=b.BENEFIT_ID
			and a.PROGRAM_ID='2218'
			and a.PLAN_ID='11'
			order by a.SORT_ID	
			*/
			lSBquery.append("select b.BENEFIT_NAME, A.LIMIT from sm.m_plan_benefit a, sm.m_benefit b ");
			lSBquery.append("where 1=1 ");
			lSBquery.append("and a.BENEFIT_ID=b.BENEFIT_ID ");
			lSBquery.append("and a.PROGRAM_ID='"+lStrProgId+"' ");
			lSBquery.append("and a.PLAN_ID='"+lStrPlanId+"' ");
			lSBquery.append("order by a.SORT_ID ");
			
			//BBApp_Common.fnPrint("Query Benefit : "+lSBquery.toString());
			BBApp_Common.fnWriteLog("CLHS_ejbREGISTRATION1", "CLHS_ejbREGISTRATION1(fnGetBenefitLim) : ","Query Benefit: "+lSBquery.toString());
			
			lPStmtFetch = lCnFetch.prepareStatement(lSBquery.toString());
			lRsFetch = lPStmtFetch.executeQuery();
			
			int i = 1;
			
			while(lRsFetch.next())
			{
				String[] st = new String[2];
				st[0] = BBApp_Common.fnGetValue(lRsFetch.getString("BENEFIT_NAME"));
				st[1] = BBApp_Common.fnGetValue(lRsFetch.getString("LIMIT"));
				lHstResult.put(""+i, st);
				i++;			
			}
			
			
						
		}
		catch(Exception e)	
		{
			e.printStackTrace();	
		}
		finally
		{
			try
			{
				if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
				if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
				if (lCnFetch!=null) {lCnFetch.close(); lCnFetch=null;}
			}
			catch(Exception e) 
			{
				BBApp_Common.fnWriteLog("CLHS_ejbREGISTRATION1", "CLHS_ejbREGISTRATION1(fnGetBenefitLim) : ","Close Exception : "+e);
			}		
		}
		
		return lHstResult;	
	}	
	
	/**
	 * fnRetrieveAnnualLimitRemaining
	 */
	public String fnRetrieveAnnualLimitRemaining(String lStrPolis, String lStrReg, String lStrReserveType, String lStrTSI) throws BBApp_Exception
	{
		StringBuffer lSbrqry	      = new StringBuffer();
		Connection lCnFetch		      = null;
		ResultSet lRsFetch		      = null;
		PreparedStatement lPStmtFetch = null;
		
		String lStrSisa = ""; 
		
		try
		{	
			lCnFetch = new BBApp_Common().OpenConnection(1);
			if(lCnFetch == null)
			{ //if connection is not established
					
				throw new BBApp_Exception("NoDataBase Connection","1500");
			}
			
			lSbrqry.delete(0, lSbrqry.length());
			
			//lSbrqry.append("SELECT SUM(A.TOTAL_AMT) TOTAL  FROM CLHC_MAST A "); 
			//lSbrqry.append("WHERE 1=1 "); 
			//lSbrqry.append("AND A.POL_NO='"+lStrPolis+"' ");
			//lSbrqry.append("AND A.CLAIMENT='"+lStrReg+"' "); 
			//lSbrqry.append("AND A.RESERVE_TYPE ="+lStrReserveType+" ");
			//lSbrqry.append("AND A.CLM_STAT='C'");
			lSbrqry.append("select sum(c.CLAIM_ACCEPTED) TOTAL from ");
			lSbrqry.append("sm.t_claim a, sm.t_claim_participant b, sm.t_claim_program c ");
			lSbrqry.append("where 1=1 ");
			lSbrqry.append("and a.REGISTER_NO=b.REGISTER_NO ");
			lSbrqry.append("and b.REGISTER_NO=c.REGISTER_NO ");
			lSbrqry.append("and b.PARTICIPANT_KLAIM_RUN=c.PARTICIPANT_KLAIM_RUN ");
			lSbrqry.append("and a.POLICY_NO='"+lStrPolis+"' ");//and b.REG_NO='479a'
			lSbrqry.append("and b.REG_NO='"+lStrReg+"' ");
			lSbrqry.append("and a.ACCEPTED_NO is not null ");
			lSbrqry.append("and a.sts_program='"+lStrReserveType+"' ");
			lSbrqry.append("group by b.PARTICIPANT_KLAIM_RUN ");
			
			BBApp_Common.fnPrint("Ubah : "+lSbrqry);
			
			lPStmtFetch = lCnFetch.prepareStatement(lSbrqry.toString());
			lRsFetch = lPStmtFetch.executeQuery();
			
			String lStrTotal = "";
			long lTotal = 0;
			
			while(lRsFetch.next())
			{
				long lSubTot = Long.parseLong((lRsFetch.getString("TOTAL")));		
				lTotal += lSubTot;
			
			
			}
			
			lStrTotal = ""+lTotal;
			
			if(lStrTotal.length() > 0)
			{
				long lLongTotal = Long.parseLong(lStrTotal);
				long lLongTSI = Long.parseLong(lStrTSI);
				
				
				
				long lLongSisa = lLongTSI - lLongTotal;
			
				if(lLongSisa <= 0)
				{
					lStrSisa = "0";		
				}
				else
				{
					lStrSisa = Long.toString(lLongSisa); 	
				}
			}
			else
			{
				return lStrTSI;	
			}
		}
		catch(Exception e)
		{
			BBApp_Common.fnWriteLog("CLHS_ejbREGISTRATION", "CLHS_ejbREGISTRATION(fnGetDataHospital) : ","Fetch Exception : "+e);
			throw new BBApp_Exception("General Exception","2000");
		}				
		finally
		{
			try
			{
				if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
				if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
				if (lCnFetch!=null) {lCnFetch.close(); lCnFetch=null;}
			}
			catch(Exception e) 
			{
				BBApp_Common.fnWriteLog("CLHS_ejbREGISTRATION1", "CLHS_ejbREGISTRATION1(fnRetrieveAnnualLimitRemaining) : ","Close Exception : "+e);
			}
		}
		
		return lStrSisa;
			
	}
	
	/**
	 * fnShowDataPolis
	 */
	public Hashtable fnShowDataPolis(String lStrPolis, String lStrReg) throws BBApp_Exception
	{
		StringBuffer lSbrqry	      = new StringBuffer();
		Connection lCnFetch		      = null;
		ResultSet lRsFetch		      = null;
		PreparedStatement lPStmtFetch = null;
		String lStrPolisOld = "";
		
		String lStrParticipantNew = "";
		String lStrParticipantOld = "";
		
		String lStrDOB = "";
		
		Hashtable lHstDataPolis = new Hashtable();
		
		try
		{
			lCnFetch = new BBApp_Common().OpenConnection(1);
			if(lCnFetch == null) { //if connection is not established
				throw new BBApp_Exception("NoDataBase Connection","1500");
			}
			
//			//query polis baru
//			lSbrqry.delete(0, lSbrqry.length());
//			lSbrqry.append("select distinct a.POLICY_NO,b.PARTICIPANT_ID,b.REG_NO,b.DOB,b.NAME,b.TGL_ENDORS,a.BEGIN_DATE,a.END_DATE,");
//			lSbrqry.append("(select hh.mcl_name from general.lst_agen gg, general.mst_client hh where gg.MCL_ID=hh.MCL_ID and gg.LAG_AGEN_ID=a.LAG_AGEN_ID) Sumber_Bisnis ,a.qq,");
//			lSbrqry.append("(");
//			lSbrqry.append("select ww.mcl_name "); 
//			lSbrqry.append("from general.lst_mo qq, general.mst_client ww ");
//			lSbrqry.append("where qq.mcl_id = ww.mcl_id ");
//			lSbrqry.append("and qq.LMO_ID = a.LMO_ID ) MO,a.POLICY_NO_OLD, "); 
//			lSbrqry.append("(");
//			lSbrqry.append("select ee.ldc_nama ");
//			lSbrqry.append("from general.lst_det_cabang ee ");
//			lSbrqry.append("where ee.ldc_id = a.ldc_id) Cabang, (select jj.MCL_NAME from general.lst_agen ii, general.mst_client jj where a.LAG_AGEN_ID=ii.LAG_AGEN_ID and a.LAG_LEADER=ii.INSTITUSI_ID and ii.MCL_ID=jj.MCL_ID) Institusi,");
//			lSbrqry.append("decode(a.PEC, null, 'null', ");
//			lSbrqry.append("1, 'Berlaku', ");
//			lSbrqry.append("2, 'Tidak Berlaku', ");
//			lSbrqry.append("3, 'Berlaku untuk Peserta Baru', ");
//			lSbrqry.append("4, 'Berlaku untuk peserta baru',5,'Berlaku prorate untuk peserta baru') PEC, d.MAX_AGE_ADULT Max_Usia_Peserta,d.MAX_AGE_ADULT Max_Usia_Pasangan,");
//			lSbrqry.append("d.MAX_AGE_CHILD,d.MIN_AGE_CHILD,'anak yg ditanggung' anak_yg_ditanggung,d.restore_benefit masa_pemulihan_jaminan,");
//			lSbrqry.append("decode(a.excess_byr_ditempat, '0', 'ASM', '1', 'Peserta', 'ASM') excess_byr_ditempat,");
//			lSbrqry.append("decode(a.HOSPITAL_PROVIDER, 0, 'Tidak', 1, 'Ya') jaminan_ip_provider,decode(a.payment_method, '0', 'Monthly', ");
//			lSbrqry.append("'1', 'Annualy') payment_method,a.DEPOSIT_HOSPITAL deposit_provider,decode(a.WAITING_PERIOD, null, null, 1, ");
//			lSbrqry.append("'Berlaku', 2, 'Tidak Berlaku', 3, 'Berlaku Prorata', 4, 'Berlaku untuk peserta baru', 5, 'Berlaku prorata untuk peserta baru') periode_tunggu,");
//			lSbrqry.append("decode(a.MARTERNITY_PARCITIPANT, null, null, 0, 'Tidak Ada', 1, 'Istri Karyawan', 2, 'Istri Karyawan dan Karyawati Menikah') peserta_melahirkan,");
//			lSbrqry.append("d.MAX_AGE_ADULT max_usia_melahirkan, 'sampai_anak_ke' sampai_anak_ke, 'pre_post' pre_dan_post, (select count(*) from t_sppa_pdf where policy_no='"+lStrPolis+"' ) check_polis   ");
//			lSbrqry.append("from sm.t_sppa a, sm.t_participant b,sm.t_plan c, sm.m_program d, sm.m_plan e, sm.m_group_program f ");
//			lSbrqry.append("where 1=1 ");
//			lSbrqry.append("and a.SPPA_NO=b.SPPA_NO "); 
//			lSbrqry.append("and b.PARTICIPANT_ID=c.PARTICIPANT_ID ");
//			lSbrqry.append("and c.SPPA_NO=a.SPPA_NO ");
//			lSbrqry.append("and b.SPPA_NO=c.SPPA_NO ");
//			lSbrqry.append("and c.PROGRAM_ID=d.PROGRAM_ID ");
//			lSbrqry.append("and c.PLAN_ID=e.PLAN_ID ");
//			lSbrqry.append("and c.PROGRAM_ID=e.PROGRAM_ID ");
//			lSbrqry.append("and e.PROGRAM_ID=d.PROGRAM_ID ");
//			lSbrqry.append("and d.GROUP_PROG_ID=f.GROUP_PROG_ID ");
//			lSbrqry.append("and a.POLICY_NO='"+lStrPolis+"' ");
//			lSbrqry.append("and b.REG_NO='"+lStrReg+"' ");
//			lSbrqry.append("and b.STS_AKTIF=1 ");
//			lSbrqry.append(" and d.GROUP_PROG_ID='1'");
//			
//			BBApp_Common.fnPrint("Data Polis : "+lSbrqry.toString());
//			

			
			
//			
//			lPStmtFetch = lCnFetch.prepareStatement(lSbrqry.toString());
//			lRsFetch = lPStmtFetch.executeQuery();
//			 
//			BBApp_cjbPOL lObjBBApp_cjbPOL = new BBApp_cjbPOL();
//			
//			
//			
//			if(lRsFetch.next())
//			{
//				
//				//select distinct a.POLICY_NO,b.REG_NO,b.DOB,b.NAME,b.TGL_ENDORS,a.BEGIN_DATE,a.END_DATE
//				//Data Polis
//				lObjBBApp_cjbPOL.setPOL_NO(BBApp_Common.fnGetValue(lRsFetch.getString("POLICY_NO")));
//				lObjBBApp_cjbPOL.setQQ(BBApp_Common.fnGetValue(lRsFetch.getString("qq")));
//				lObjBBApp_cjbPOL.setCLAIMANT_NAME(BBApp_Common.fnGetValue(lRsFetch.getString("NAME")));
//				lObjBBApp_cjbPOL.setREG_NO(BBApp_Common.fnGetValue(lRsFetch.getString("REG_NO")));
//				lObjBBApp_cjbPOL.setEFF_DATE(BBApp_Common.fnGetValue(lRsFetch.getString("TGL_ENDORS")));
//				lObjBBApp_cjbPOL.setEXP_DATE(BBApp_Common.fnGetValue(lRsFetch.getString("END_DATE")));
//				lObjBBApp_cjbPOL.setBEGIN_DATE(BBApp_Common.fnGetValue(lRsFetch.getString("BEGIN_DATE")));
//				lObjBBApp_cjbPOL.setBANK_NAME(BBApp_Common.fnGetValue(lRsFetch.getString("Sumber_Bisnis")));
//				lObjBBApp_cjbPOL.setMO(BBApp_Common.fnGetValue(lRsFetch.getString("MO")));
//				lObjBBApp_cjbPOL.setCabang(BBApp_Common.fnGetValue(lRsFetch.getString("Cabang")));
//				lObjBBApp_cjbPOL.setInstitusi(BBApp_Common.fnGetValue(lRsFetch.getString("Institusi")));
//				lObjBBApp_cjbPOL.setDOB(BBApp_Common.fnGetValue(lRsFetch.getString("DOB")));
//				
//				lStrDOB = BBApp_Common.fnGetDateDDMMYYYY(lObjBBApp_cjbPOL.getDOB());
//				//PEC
//				lObjBBApp_cjbPOL.setPEC(BBApp_Common.fnGetValue(lRsFetch.getString("PEC")));
//				lObjBBApp_cjbPOL.setMAX_AGE_PESERTA(BBApp_Common.fnGetValue(lRsFetch.getString("Max_Usia_Peserta")));
//				lObjBBApp_cjbPOL.setMAX_AGE_PASANGAN(BBApp_Common.fnGetValue(lRsFetch.getString("Max_Usia_Pasangan")));
//				lObjBBApp_cjbPOL.setMIN_AGE_CHILD(BBApp_Common.fnGetValue(lRsFetch.getString("MIN_AGE_CHILD")));
//				lObjBBApp_cjbPOL.setMAX_AGE_CHILD(BBApp_Common.fnGetValue(lRsFetch.getString("MAX_AGE_CHILD")));
//				lObjBBApp_cjbPOL.setRESTORE_BENEFIT(BBApp_Common.fnGetValue(lRsFetch.getString("masa_pemulihan_jaminan")));
//				lObjBBApp_cjbPOL.setSUM_CHILD(BBApp_Common.fnGetValue(lRsFetch.getString("anak_yg_ditanggung")));
//				lObjBBApp_cjbPOL.setEXCESS_PAYMENT_PLACE(BBApp_Common.fnGetValue(lRsFetch.getString("excess_byr_ditempat")));
//				lObjBBApp_cjbPOL.setJAMINAN_IP_PROVIDER(BBApp_Common.fnGetValue(lRsFetch.getString("jaminan_ip_provider")));
//				lObjBBApp_cjbPOL.setPAYMENT_METHOD(BBApp_Common.fnGetValue(lRsFetch.getString("payment_method")));
//				lObjBBApp_cjbPOL.setDEPOSIT_PROVIDER(BBApp_Common.fnGetValue(lRsFetch.getString("deposit_provider")));
//				lObjBBApp_cjbPOL.setWAITING_PERIOD(BBApp_Common.fnGetValue(lRsFetch.getString("periode_tunggu")));
//				
//			
//				lObjBBApp_cjbPOL.setMARTERNITY_PARCITIPANT(BBApp_Common.fnGetValue(lRsFetch.getString("peserta_melahirkan")));
//				lObjBBApp_cjbPOL.setMAX_AGE_MATERNITY(BBApp_Common.fnGetValue(lRsFetch.getString("max_usia_melahirkan")));
//				lObjBBApp_cjbPOL.setPRE_AND_POST(BBApp_Common.fnGetValue(lRsFetch.getString("pre_dan_post")));
//				lObjBBApp_cjbPOL.setUNTIL_CHILD_TO(BBApp_Common.fnGetValue(lRsFetch.getString("sampai_anak_ke")));
//				
//				lStrPolisOld = BBApp_Common.fnGetValue(lRsFetch.getString("POLICY_NO_OLD"));
//				
//				lStrParticipantNew = BBApp_Common.fnGetValue(lRsFetch.getString("PARTICIPANT_ID"));
//				String lStrCheckNew = BBApp_Common.fnGetValue(lRsFetch.getString("check_polis"));
//				
//				
//				lHstDataPolis.put("NEW_POLIS", lObjBBApp_cjbPOL);
//				lHstDataPolis.put("CHECK_NEW_POLIS", lStrCheckNew);
//			}
			
//			if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
//			if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
			
			//Plan Baru semua
			lSbrqry.delete(0, lSbrqry.length());
			//lSbrqry.append("SELECT  G.PROGRAM_ID, G.PLAN_ID, G.PLAN_NAME,E.PROGRAM_NAME,C.TSI,C.NET_PREMIUM ");
			lSbrqry.append("SELECT  G.PROGRAM_ID, G.PLAN_ID, G.PLAN_NAME,E.PROGRAM_NAME,C.TSI,C.NET_PREMIUM, ");
			lSbrqry.append("( ");
			lSbrqry.append("select sum(a1.TOT_ACCEPTED_CLAIM) ");
			lSbrqry.append("from sm.t_claim a1, sm.t_claim_participant b1, sm.t_request_data c1, sm.t_request d1 ");
			lSbrqry.append("where 1=1 ");
			lSbrqry.append("and a1.POLICY_NO=A.POLICY_NO ");
			lSbrqry.append("and b1.REG_NO=B.REG_NO ");
			lSbrqry.append("and a1.REGISTER_NO=b1.REGISTER_NO ");
			lSbrqry.append("and b1.PROGRAM_ID=C.PROGRAM_ID ");
			lSbrqry.append("and b1.PLAN_ID=C.PLAN_ID ");
			lSbrqry.append("and a1.ACCEPTED_NO is not null ");
			lSbrqry.append("and a1.REGISTER_NO=c1.REGISTER_NO ");
			lSbrqry.append("and c1.REQUEST_NO=d1.REQUEST_NO ");
			lSbrqry.append("and d1.TGL_LUNAS is not null ");
			lSbrqry.append(") KLAIM_ACCEPTED, D.GROUP_PROG_ID, d.group_prog_name2 ");
			lSbrqry.append("FROM SM.T_SPPA A, SM.T_PARTICIPANT B,SM.T_PLAN C,SM.M_GROUP_PROGRAM D,SM.M_PROGRAM E, ");
 			lSbrqry.append("SM.M_PLAN_BENEFIT F, SM.M_PLAN G "); 
 			lSbrqry.append("WHERE A.SPPA_NO=B.SPPA_NO "); 
 			lSbrqry.append("AND B.PARTICIPANT_ID=C.PARTICIPANT_ID "); 
 			lSbrqry.append("AND A.SPPA_NO=C.SPPA_NO "); 
 			lSbrqry.append("AND C.PROGRAM_ID=E.PROGRAM_ID "); 
 			lSbrqry.append("AND E.GROUP_PROG_ID=D.GROUP_PROG_ID "); 
 			lSbrqry.append("AND F.PROGRAM_ID=C.PROGRAM_ID "); 
 			lSbrqry.append("AND F.PROGRAM_ID=E.PROGRAM_ID "); 
			lSbrqry.append("AND G.PLAN_ID=C.PLAN_ID "); 
			lSbrqry.append("AND G.PLAN_ID=F.PLAN_ID "); 
			lSbrqry.append("AND G.PLAN_ID=F.PLAN_ID "); 
			lSbrqry.append("AND G.PROGRAM_ID=E.PROGRAM_ID "); 
			lSbrqry.append("AND G.PROGRAM_ID=F.PROGRAM_ID "); 
			lSbrqry.append("AND G.PROGRAM_ID=C.PROGRAM_ID "); 
			lSbrqry.append("AND F.SORT_ID='1' "); 
			lSbrqry.append("AND B.STS_AKTIF='1' ");
			lSbrqry.append("AND A.POLICY_NO='"+lStrPolis+"' "); 
			lSbrqry.append("AND B.REG_NO='"+lStrReg+"' ");
			lSbrqry.append("order by D.GROUP_PROG_ID");
			
			BBApp_Common.fnPrint(lSbrqry.toString());
			
			lPStmtFetch = lCnFetch.prepareStatement(lSbrqry.toString());
			lRsFetch = lPStmtFetch.executeQuery();
			
			Hashtable lHstPlan = new Hashtable();
			
			int i = 1;
			
			while(lRsFetch.next())
			{
				
				String lStrSisaLim = "";
				
				String[] st = new String[8];
				st[0] = BBApp_Common.fnGetValue(lRsFetch.getString("PROGRAM_NAME"));
				st[1] = BBApp_Common.fnGetValue(lRsFetch.getString("PLAN_NAME"));
				st[2] = BBApp_Common.fnGetValue(lRsFetch.getString("TSI"));
				st[3] = BBApp_Common.fnGetValue(lRsFetch.getString("NET_PREMIUM"));
				st[4] = BBApp_Common.fnGetValue(lRsFetch.getString("PROGRAM_ID"));
				st[5] = BBApp_Common.fnGetValue(lRsFetch.getString("PLAN_ID"));
				st[7] = BBApp_Common.fnGetValue(lRsFetch.getString("GROUP_PROG_NAME2"));
				
				
				String lStrSisa = fnRetrieveAnnualLimitRemaining(lStrPolis, lStrReg, BBApp_Common.fnGetValue(lRsFetch.getString("GROUP_PROG_ID")), st[2]);
				
				//added By Edo Yuliando 20-04-2010
				lStrSisaLim = BBApp_Common.fnGetValue(lRsFetch.getString("KLAIM_ACCEPTED"));
				
				//if(lStrSisaLim.equals(""))
				//{
				//	lStrSisaLim = "0";	
				//}
				
				//String lStrTSI = st[2]; 
				
				//if(lStrTSI.equals(""))
				//{
				//	lStrTSI = "0";	
				//}
				
//				long lTsi = Long.parseLong(lStrTSI); 
//				long lAccepted = Long.parseLong(lStrSisaLim);
//				long lSsLm = lTsi - lAccepted; 
//				
//				if(lSsLm < 0)
//				{
//					lSsLm = 0;	
//				}
				
				st[6] = lStrSisa;
				//akhir
				
				lHstPlan.put(""+i, st);
				i++;			
			}
			lHstDataPolis.put("NEW_PLAN", lHstPlan);
			
//			lSbrqry.delete(0, lSbrqry.length());
//			if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
//			if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
//			
//			Hashtable lHstEndNew = new Hashtable();
//			
//			
//			lSbrqry.append("select a.SPPE_NO, a.EDM_NO, c.EDM_DESC,b.EFFECTIVE_DATE ");
//			lSbrqry.append("from sm.t_sppe a, sm.t_participant_edm b, sm.m_mst_edm c where 1=1 ");
//			lSbrqry.append("and a.SPPE_NO=b.SPPE_NO ");
//			lSbrqry.append("and b.STS_EDM=c.STS_EDM ");
//			lSbrqry.append("and a.POLICY_NO='"+lObjBBApp_cjbPOL.getPOL_NO()+"' ");
//			lSbrqry.append("and b.PARTICIPANT_ID='"+lStrParticipantNew+"'");
//			
//			BBApp_Common.fnPrint(lSbrqry.toString());
//			
//			lPStmtFetch = lCnFetch.prepareStatement(lSbrqry.toString());
//			lRsFetch = lPStmtFetch.executeQuery();
//			i = 1;
//			
//			while(lRsFetch.next())
//			{
//				String[] st = new String[4];
//				st[0] = BBApp_Common.fnGetValue(lRsFetch.getString("SPPE_NO"));
//				st[1] = BBApp_Common.fnGetValue(lRsFetch.getString("EDM_NO"));
//				st[2] = BBApp_Common.fnGetValue(lRsFetch.getString("EDM_DESC"));
//				st[3] = BBApp_Common.fnGetValue(lRsFetch.getString("EFFECTIVE_DATE"));
//				
//				lHstEndNew.put(""+i, st);
//				
//				i++;						
//			}
//			
//			lHstDataPolis.put("ENDORSEMENT_NEW", lHstEndNew);
//			
//			
//			lSbrqry.delete(0, lSbrqry.length());
//			if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
//			if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
//			
//			//Polis lama
//			lSbrqry.append("select distinct a.POLICY_NO,b.PARTICIPANT_ID,b.REG_NO,b.DOB,b.NAME,b.TGL_ENDORS,a.BEGIN_DATE,a.END_DATE,");
//			lSbrqry.append("(select hh.mcl_name from general.lst_agen gg, general.mst_client hh where gg.MCL_ID=hh.MCL_ID and gg.LAG_AGEN_ID=a.LAG_AGEN_ID) Sumber_Bisnis ,a.qq,");
//			lSbrqry.append("(");
//			lSbrqry.append("select ww.mcl_name "); 
//			lSbrqry.append("from general.lst_mo qq, general.mst_client ww ");
//			lSbrqry.append("where qq.mcl_id = ww.mcl_id ");
//			lSbrqry.append("and qq.LMO_ID = a.LMO_ID ) MO, "); 
//			
//			lSbrqry.append("(");
//			lSbrqry.append("select ee.ldc_nama ");
//			lSbrqry.append("from general.lst_det_cabang ee ");
//			lSbrqry.append("where ee.ldc_id = a.ldc_id) Cabang,(select jj.MCL_NAME from general.lst_agen ii, general.mst_client jj where a.LAG_AGEN_ID=ii.LAG_AGEN_ID and a.LAG_LEADER=ii.INSTITUSI_ID and ii.MCL_ID=jj.MCL_ID) Institusi,");
//			lSbrqry.append("decode(a.PEC, null, 'null', ");
//			lSbrqry.append("1, 'Berlaku', ");
//			lSbrqry.append("2, 'Tidak Berlaku', ");
//			lSbrqry.append("3, 'Berlaku untuk Peserta Baru', ");
//			lSbrqry.append("4, 'Berlaku untuk peserta baru',5,'Berlaku prorate untuk peserta baru') PEC, d.MAX_AGE_ADULT Max_Usia_Peserta,d.MAX_AGE_ADULT Max_Usia_Pasangan,");
//			lSbrqry.append("d.MAX_AGE_CHILD,d.MIN_AGE_CHILD,'anak yg ditanggung' anak_yg_ditanggung,d.restore_benefit masa_pemulihan_jaminan,");
//			lSbrqry.append("decode(a.excess_byr_ditempat, '0', 'ASM', '1', 'Peserta', 'ASM') excess_byr_ditempat,");
//			lSbrqry.append("decode(a.HOSPITAL_PROVIDER, 0, 'Tidak', 1, 'Ya') jaminan_ip_provider,decode(a.payment_method, '0', 'Monthly', ");
//			lSbrqry.append("'1', 'Annualy') payment_method,a.DEPOSIT_HOSPITAL deposit_provider,decode(a.WAITING_PERIOD, null, null, 1, ");
//			lSbrqry.append("'Berlaku', 2, 'Tidak Berlaku', 3, 'Berlaku Prorata', 4, 'Berlaku untuk peserta baru', 5, 'Berlaku prorata untuk peserta baru') periode_tunggu,");
//			lSbrqry.append("decode(a.MARTERNITY_PARCITIPANT, null, null, 0, 'Tidak Ada', 1, 'Istri Karyawan', 2, 'Istri Karyawan dan Karyawati Menikah') peserta_melahirkan,");
//			lSbrqry.append("d.MAX_AGE_ADULT max_usia_melahirkan, 'sampai_anak_ke' sampai_anak_ke, 'pre_post' pre_dan_post, (select count(*) from t_sppa_pdf where policy_no=a.POLICY_NO ) check_polis   ");
//			lSbrqry.append("from sm.t_sppa a, sm.t_participant b,sm.t_plan c, sm.m_program d, sm.m_plan e, sm.m_group_program f ");
//			lSbrqry.append("where 1=1 ");
//			lSbrqry.append("and a.SPPA_NO=b.SPPA_NO "); 
//			lSbrqry.append("and b.PARTICIPANT_ID=c.PARTICIPANT_ID ");
//			lSbrqry.append("and c.SPPA_NO=a.SPPA_NO ");
//			lSbrqry.append("and b.SPPA_NO=c.SPPA_NO ");
//			lSbrqry.append("and c.PROGRAM_ID=d.PROGRAM_ID ");
//			lSbrqry.append("and c.PLAN_ID=e.PLAN_ID ");
//			lSbrqry.append("and c.PROGRAM_ID=e.PROGRAM_ID ");
//			lSbrqry.append("and e.PROGRAM_ID=d.PROGRAM_ID ");
//			lSbrqry.append("and d.GROUP_PROG_ID=f.GROUP_PROG_ID ");
//			lSbrqry.append("and a.POLICY_NO='"+lStrPolisOld+"' ");
//			lSbrqry.append("and b.NAME='"+lObjBBApp_cjbPOL.getCLAIMANT_NAME()+"' ");
//			lSbrqry.append("and b.DOB = to_date('"+lStrDOB+"', 'dd/mm/yyyy') ");
//			lSbrqry.append("and d.GROUP_PROG_ID='1'");
//			
//			BBApp_Common.fnPrint("SQL Query : "+lSbrqry.toString());
//			
//			lPStmtFetch = lCnFetch.prepareStatement(lSbrqry.toString());
//			lRsFetch = lPStmtFetch.executeQuery();
//			
//			lObjBBApp_cjbPOL = new BBApp_cjbPOL();
//			
//			if(lRsFetch.next())
//			{
//				
//				//select distinct a.POLICY_NO,b.REG_NO,b.DOB,b.NAME,b.TGL_ENDORS,a.BEGIN_DATE,a.END_DATE
//				//Data Polis
//				lObjBBApp_cjbPOL.setPOL_NO(BBApp_Common.fnGetValue(lRsFetch.getString("POLICY_NO")));
//				lObjBBApp_cjbPOL.setQQ(BBApp_Common.fnGetValue(lRsFetch.getString("qq")));
//				lObjBBApp_cjbPOL.setCLAIMANT_NAME(BBApp_Common.fnGetValue(lRsFetch.getString("NAME")));
//				lObjBBApp_cjbPOL.setREG_NO(BBApp_Common.fnGetValue(lRsFetch.getString("REG_NO")));
//				lObjBBApp_cjbPOL.setEFF_DATE(BBApp_Common.fnGetValue(lRsFetch.getString("TGL_ENDORS")));
//				lObjBBApp_cjbPOL.setEXP_DATE(BBApp_Common.fnGetValue(lRsFetch.getString("END_DATE")));
//				lObjBBApp_cjbPOL.setBEGIN_DATE(BBApp_Common.fnGetValue(lRsFetch.getString("BEGIN_DATE")));
//				lObjBBApp_cjbPOL.setBANK_NAME(BBApp_Common.fnGetValue(lRsFetch.getString("Sumber_Bisnis")));
//				lObjBBApp_cjbPOL.setMO(BBApp_Common.fnGetValue(lRsFetch.getString("MO")));
//				lObjBBApp_cjbPOL.setCabang(BBApp_Common.fnGetValue(lRsFetch.getString("Cabang")));
//				lObjBBApp_cjbPOL.setInstitusi(BBApp_Common.fnGetValue(lRsFetch.getString("Institusi")));
//				lObjBBApp_cjbPOL.setDOB(BBApp_Common.fnGetValue(lRsFetch.getString("DOB")));
//				//PEC
//				lObjBBApp_cjbPOL.setPEC(BBApp_Common.fnGetValue(lRsFetch.getString("PEC")));
//				lObjBBApp_cjbPOL.setMAX_AGE_PESERTA(BBApp_Common.fnGetValue(lRsFetch.getString("Max_Usia_Peserta")));
//				lObjBBApp_cjbPOL.setMAX_AGE_PASANGAN(BBApp_Common.fnGetValue(lRsFetch.getString("Max_Usia_Pasangan")));
//				lObjBBApp_cjbPOL.setMIN_AGE_CHILD(BBApp_Common.fnGetValue(lRsFetch.getString("MIN_AGE_CHILD")));
//				lObjBBApp_cjbPOL.setMAX_AGE_CHILD(BBApp_Common.fnGetValue(lRsFetch.getString("MAX_AGE_CHILD")));
//				lObjBBApp_cjbPOL.setRESTORE_BENEFIT(BBApp_Common.fnGetValue(lRsFetch.getString("masa_pemulihan_jaminan")));
//				lObjBBApp_cjbPOL.setSUM_CHILD(BBApp_Common.fnGetValue(lRsFetch.getString("anak_yg_ditanggung")));
//				lObjBBApp_cjbPOL.setEXCESS_PAYMENT_PLACE(BBApp_Common.fnGetValue(lRsFetch.getString("excess_byr_ditempat")));
//				lObjBBApp_cjbPOL.setJAMINAN_IP_PROVIDER(BBApp_Common.fnGetValue(lRsFetch.getString("jaminan_ip_provider")));
//				lObjBBApp_cjbPOL.setPAYMENT_METHOD(BBApp_Common.fnGetValue(lRsFetch.getString("payment_method")));
//				lObjBBApp_cjbPOL.setDEPOSIT_PROVIDER(BBApp_Common.fnGetValue(lRsFetch.getString("deposit_provider")));
//				lObjBBApp_cjbPOL.setWAITING_PERIOD(BBApp_Common.fnGetValue(lRsFetch.getString("periode_tunggu")));
//				
//			
//				lObjBBApp_cjbPOL.setMARTERNITY_PARCITIPANT(BBApp_Common.fnGetValue(lRsFetch.getString("peserta_melahirkan")));
//				lObjBBApp_cjbPOL.setMAX_AGE_MATERNITY(BBApp_Common.fnGetValue(lRsFetch.getString("max_usia_melahirkan")));
//				lObjBBApp_cjbPOL.setPRE_AND_POST(BBApp_Common.fnGetValue(lRsFetch.getString("pre_dan_post")));
//				lObjBBApp_cjbPOL.setUNTIL_CHILD_TO(BBApp_Common.fnGetValue(lRsFetch.getString("sampai_anak_ke")));
//				
//				
//				lStrParticipantOld = BBApp_Common.fnGetValue(lRsFetch.getString("PARTICIPANT_ID"));
//				String lStrCheckOld = BBApp_Common.fnGetValue(lRsFetch.getString("check_polis"));
//				
//				lHstDataPolis.put("OLD_POLIS", lObjBBApp_cjbPOL);
//				lHstDataPolis.put("CHECK_OLD_POLIS", lStrCheckOld);
//			}
//			
//			//Plan lama
//			
//			lSbrqry.delete(0, lSbrqry.length());
//			if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
//			if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
//			
//			
//			
//			//lSbrqry.append("SELECT  G.PROGRAM_ID, G.PLAN_ID,G.PLAN_NAME,E.PROGRAM_NAME,C.TSI,C.NET_PREMIUM ");
//			lSbrqry.append("SELECT  G.PROGRAM_ID, G.PLAN_ID,G.PLAN_NAME,E.PROGRAM_NAME,C.TSI,C.NET_PREMIUM ");
//			lSbrqry.append(",( ");
//			lSbrqry.append("select sum(a1.TOT_ACCEPTED_CLAIM) ");
//			lSbrqry.append("from sm.t_claim a1, sm.t_claim_participant b1, sm.t_request_data c1, sm.t_request d1 ");
//			lSbrqry.append("where 1=1 ");
//			lSbrqry.append("and a1.POLICY_NO=A.POLICY_NO ");
//			lSbrqry.append("and b1.REG_NO=B.REG_NO ");
//			lSbrqry.append("and a1.REGISTER_NO=b1.REGISTER_NO ");
//			lSbrqry.append("and b1.PROGRAM_ID=C.PROGRAM_ID ");
//			lSbrqry.append("and b1.PLAN_ID=C.PLAN_ID ");
//			lSbrqry.append("and a1.ACCEPTED_NO is not null ");
//			lSbrqry.append("and a1.REGISTER_NO=c1.REGISTER_NO ");
//			lSbrqry.append("and c1.REQUEST_NO=d1.REQUEST_NO ");
//			lSbrqry.append("and d1.TGL_LUNAS is not null ");
//			lSbrqry.append(") KLAIM_ACCEPTED,D.GROUP_PROG_ID ");
//			lSbrqry.append("FROM SM.T_SPPA A, SM.T_PARTICIPANT B,SM.T_PLAN C,SM.M_GROUP_PROGRAM D,SM.M_PROGRAM E, ");
// 			lSbrqry.append("SM.M_PLAN_BENEFIT F, SM.M_PLAN G "); 
// 			lSbrqry.append("WHERE A.SPPA_NO=B.SPPA_NO "); 
// 			lSbrqry.append("AND B.PARTICIPANT_ID=C.PARTICIPANT_ID "); 
// 			lSbrqry.append("AND A.SPPA_NO=C.SPPA_NO "); 
// 			lSbrqry.append("AND C.PROGRAM_ID=E.PROGRAM_ID "); 
// 			lSbrqry.append("AND E.GROUP_PROG_ID=D.GROUP_PROG_ID "); 
// 			lSbrqry.append("AND F.PROGRAM_ID=C.PROGRAM_ID "); 
// 			lSbrqry.append("AND F.PROGRAM_ID=E.PROGRAM_ID "); 
//			lSbrqry.append("AND G.PLAN_ID=C.PLAN_ID "); 
//			lSbrqry.append("AND G.PLAN_ID=F.PLAN_ID "); 
//			lSbrqry.append("AND G.PLAN_ID=F.PLAN_ID "); 
//			lSbrqry.append("AND G.PROGRAM_ID=E.PROGRAM_ID "); 
//			lSbrqry.append("AND G.PROGRAM_ID=F.PROGRAM_ID "); 
//			lSbrqry.append("AND G.PROGRAM_ID=C.PROGRAM_ID "); 
//			lSbrqry.append("AND F.SORT_ID='1' "); 
//			lSbrqry.append("AND B.STS_AKTIF='1' ");
//			lSbrqry.append("AND A.POLICY_NO='"+lStrPolisOld +"' "); 
//			lSbrqry.append("AND B.NAME='"+lObjBBApp_cjbPOL.getCLAIMANT_NAME()+"' ");
//			lSbrqry.append("order by D.GROUP_PROG_ID");
//			
//			BBApp_Common.fnPrint(lSbrqry.toString());
//			
//			
//			Hashtable lHstplan1 = new Hashtable();
//			
//			i = 1;
//			
//			
//			lPStmtFetch = lCnFetch.prepareStatement(lSbrqry.toString());
//			lRsFetch = lPStmtFetch.executeQuery();
//			
//			
//			while(lRsFetch.next())
//			{
//				String lStrSisaLim = "";
//				String[] st = new String[7];
//				st[0] = BBApp_Common.fnGetValue(lRsFetch.getString("PROGRAM_NAME"));
//				st[1] = BBApp_Common.fnGetValue(lRsFetch.getString("PLAN_NAME"));
//				st[2] = BBApp_Common.fnGetValue(lRsFetch.getString("TSI"));
//				st[3] = BBApp_Common.fnGetValue(lRsFetch.getString("NET_PREMIUM"));
//				st[4] = BBApp_Common.fnGetValue(lRsFetch.getString("PROGRAM_ID"));
//				st[5] = BBApp_Common.fnGetValue(lRsFetch.getString("PLAN_ID"));
//				
//				
//				//added By Edo Yuliando 20-04-2010
//				lStrSisaLim = BBApp_Common.fnGetValue(lRsFetch.getString("KLAIM_ACCEPTED"));
//				
//				String lStrSisa = fnRetrieveAnnualLimitRemaining(lStrPolis, lStrReg,BBApp_Common.fnGetValue(lRsFetch.getString("GROUP_PROG_ID")), st[2]);
////				if(lStrSisaLim.equals(""))vv
////				{
////					lStrSisaLim = "0";	
////				}
////				
////				String lStrTSI = st[2]; 
////				
////				if(lStrTSI.equals(""))
////				{
////					lStrTSI = "0";	
////				}
////				
////				long lTsi = Long.parseLong(lStrTSI); 
////				long lAccepted = Long.parseLong(lStrSisaLim);
////				long lSsLm = lTsi - lAccepted; 
////				
////				if(lSsLm < 0)
////				{
////					lSsLm = 0;	
////				}
//				
//				st[6] = lStrSisa;
//				//akhir
//				
//				lHstplan1.put(""+i, st);
//				i++;			
//			}
//			lHstDataPolis.put("OLD_PLAN", lHstplan1);
//			
//			lSbrqry.delete(0, lSbrqry.length());
//			if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
//			if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
//			
//			Hashtable lHstEndOld = new Hashtable();
//			
//			lSbrqry.append("select a.SPPE_NO, a.EDM_NO, c.EDM_DESC,b.EFFECTIVE_DATE ");
//			lSbrqry.append("from sm.t_sppe a, sm.t_participant_edm b, sm.m_mst_edm c where 1=1 ");
//			lSbrqry.append("and a.SPPE_NO=b.SPPE_NO ");
//			lSbrqry.append("and b.STS_EDM=c.STS_EDM ");
//			lSbrqry.append("and a.POLICY_NO='"+lObjBBApp_cjbPOL.getPOL_NO()+"' ");
//			lSbrqry.append("and b.PARTICIPANT_ID='"+lStrParticipantOld+"'");
//			
//			BBApp_Common.fnPrint(lSbrqry.toString());
//			
//			lPStmtFetch = lCnFetch.prepareStatement(lSbrqry.toString());
//			lRsFetch = lPStmtFetch.executeQuery();
//			i = 1;
//			
//			while(lRsFetch.next())
//			{
//				String[] st = new String[4];
//				st[0] = BBApp_Common.fnGetValue(lRsFetch.getString("SPPE_NO"));
//				st[1] = BBApp_Common.fnGetValue(lRsFetch.getString("EDM_NO"));
//				st[2] = BBApp_Common.fnGetValue(lRsFetch.getString("EDM_DESC"));
//				st[3] = BBApp_Common.fnGetValue(lRsFetch.getString("EFFECTIVE_DATE"));
//				
//				lHstEndOld.put(""+i, st);
//				
//				i++;						
//			}
//			
//			lHstDataPolis.put("ENDORSEMENT_OLD", lHstEndOld);
//			
//			
//			BBApp_Common.fnPrint("EJB 1");
		
						
		}
		catch(Exception ex)
		{
			BBApp_Common.fnWriteLog("CLHS_ejbREGISTRATION", "CLHS_ejbREGISTRATION(fnShowDatapolis) : ","Fetch Exception : "+ex);
			throw new BBApp_Exception("General Exception","2000");
		}
		finally
		{
			try
			{
				if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
				if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
				if (lCnFetch!=null) {lCnFetch.close(); lCnFetch=null;}
			}
			catch(Exception e) 
			{
				BBApp_Common.fnWriteLog("CLHS_ejbREGISTRATION1", "CLHS_ejbREGISTRATION1(fnShowDataPolis) : ","Close Exception : "+e);
			}
		}
		return lHstDataPolis;	
	}
	
	
	/**
	 * fnRetrieveDetailPeserta
	 */
	public BBApp_cjbBBApp fnRetrieveDetailPeserta(BBApp_cjbBBApp lObjBBApp_cjbBBApp) throws BBApp_Exception {
		Connection lCnFetch		      = null;	
		PreparedStatement lPStmtFetch = null;		
		ResultSet lRsFetch		      = null;
		StringBuffer lSBquery 		  = new StringBuffer();
		ResultSetMetaData rsmd 		  = null;
		
		String strNoKartu		= "";
		String strNoPolis		= "";
		String strNoReg			= "";
		String strNama			= "";
		String strUmur			= "";
		String strTglLahir		= "";
		String strPerusahaan	= "";
		
//		BBApp_Common.fnWriteLog("BBApp_Business","fnRetrieveDetailPeserta: ","Cek 1");
		
		if(lObjBBApp_cjbBBApp == null)
				throw new BBApp_Exception("Cjb Value is null","0000");
		else {
				strNoKartu		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoKartu());
				strNoPolis		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoPolis());
				strNoReg		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoReg());
				strNama			= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNama());
				strUmur			= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrUmur());
				strTglLahir		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrTglLahir());
				strPerusahaan	= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrPerusahaan());
		}
		
//		BBApp_Common.fnWriteLog("BBApp_Business","fnRetrieveDetailPeserta: ","Cek 2");
		
		BBApp_cjbBBApp lObjBBApp_cjbBBAppResult = new BBApp_cjbBBApp();
		
//		BBApp_Common.fnWriteLog("BBApp_Business","fnRetrieveDetailPeserta: ","Cek 3");
				
		try {
			lCnFetch = new BBApp_Common().OpenConnection(1);
			if(lCnFetch == null) //if connection is not established
				throw new BBApp_Exception("NoDataBase Connection","1500");
			
//			BBApp_Common.fnWriteLog("BBApp_Business","fnRetrieveDetailPeserta: ","Cek 4");
			
			lSBquery.append("select distinct a.policy_no, ");
			lSBquery.append("                a.qq, ");
			lSBquery.append("                a.begin_date, ");
			lSBquery.append("                a.end_date, ");
			lSBquery.append("                b.reg_no, ");
			lSBquery.append("                b.name, ");
			lSBquery.append("                b.begin_date begin_date_participant, ");
			lSBquery.append("                b.dob, ");
			
			lSBquery.append("TRUNC(MONTHS_BETWEEN(TO_DATE(TO_CHAR(SYSDATE, 'dd/mm/yyyy'), 'dd/mm/yyyy'),  ");
			lSBquery.append("TO_DATE(TO_CHAR(b.DOB, 'dd/mm/yyyy'),'dd/mm/YYYY'))/12) Age, ");
			
			lSBquery.append("                h.group_prog_id, ");
			lSBquery.append("                h.group_prog_name, ");
			lSBquery.append("                d.plan_name, ");
			lSBquery.append("                c.program_id, ");
			lSBquery.append("                c.plan_id, ");
			lSBquery.append("                c.tsi, ");
			lSBquery.append("                '' excess, ");
			lSBquery.append("                a.mcl_id, ");
			lSBquery.append("                a.lbu_id, ");
			lSBquery.append("                a.ldc_id ");
			lSBquery.append("                , b.card_no ");
			lSBquery.append("  from sm.t_sppa          a, ");
			lSBquery.append("       sm.t_participant   b, ");
			lSBquery.append("       sm.t_plan          c, ");
			lSBquery.append("       sm.m_plan          d, ");
			lSBquery.append("       sm.m_program       g, ");
			lSBquery.append("       sm.m_group_program h ");
			lSBquery.append(" where 1 = 1 ");
			lSBquery.append("   AND b.sppa_no = a.sppa_no ");
			lSBquery.append("   AND c.sppa_no = a.sppa_no ");
			lSBquery.append("   AND c.participant_id = b.participant_id ");
			lSBquery.append("   AND d.program_id = c.program_id ");
			lSBquery.append("   AND d.plan_id = c.plan_id ");
			lSBquery.append("   AND g.program_id = c.program_id ");
			lSBquery.append("   AND h.group_prog_id = g.group_prog_id ");
			
			//lSBquery.append("   AND (b.card_no = '"+strNoKartu+"' ");
			//lSBquery.append("   or (a.policy_no = '"+strNoPolis+"' ");
			//lSBquery.append("   AND b.reg_no = '"+strNoReg+"') ");
			//lSBquery.append("   AND b.dob = to_date('"+strTglLahir+"', 'dd/mm/yyyy')) ");
			
			lSBquery.append("   AND (upper(trim(b.name)) = upper(trim('"+strNama+"')) "); 
			lSBquery.append("   or (b.card_no = '"+strNoKartu+"' ");
			lSBquery.append("   or (a.policy_no = '"+strNoPolis+"' ");
			lSBquery.append("   AND b.reg_no = '"+strNoReg+"') ");
			lSBquery.append("   AND b.dob = to_date('"+strTglLahir+"', 'dd/mm/yyyy'))) ");
			
			lSBquery.append("   AND c.sppa_no = b.sppa_no ");
			lSBquery.append("   AND sysdate between b.begin_date and b.end_date ");
			lSBquery.append(" order by to_number(h.group_prog_id) ");
					
			//BBApp_Common.fnPrint("[BBApp_Business] fnRetrieveDetailPeserta: " + lSBquery.toString());
			BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnRetrieveDetailPeserta) : "," " + lSBquery.toString());
			
			lPStmtFetch = lCnFetch.prepareStatement(lSBquery.toString());
			lRsFetch = lPStmtFetch.executeQuery();	
			
			while(lRsFetch.next()) {
				lObjBBApp_cjbBBAppResult.setStrNoPolis(BBApp_Common.fnGetValue(lRsFetch.getString("policy_no")));
				lObjBBApp_cjbBBAppResult.setStrNoReg(BBApp_Common.fnGetValue(lRsFetch.getString("reg_no")));
				lObjBBApp_cjbBBAppResult.setStrNama(BBApp_Common.fnGetValue(lRsFetch.getString("name")));
				lObjBBApp_cjbBBAppResult.setStrUmur(BBApp_Common.fnGetValue(lRsFetch.getString("Age")));
				lObjBBApp_cjbBBAppResult.setStrTglLahir(BBApp_Common.fnGetDateDDMMMYYYY(lRsFetch.getString("dob")));
				lObjBBApp_cjbBBAppResult.setStrPerusahaan(BBApp_Common.fnGetValue(lRsFetch.getString("qq")));
				lObjBBApp_cjbBBAppResult.setStrNoKartu(BBApp_Common.fnGetValue(lRsFetch.getString("card_no")));
			}
			
			//lObjBBApp_cjbBBAppResult.setStrNoKartu(strNoKartu);
				
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
				if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
				if (lCnFetch!=null) {lCnFetch.close(); lCnFetch=null;}
			}
			catch(Exception e) 
			{
				BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnRetrieveDetailPeserta) : ","Close Exception : "+e);
			}		
		}
		return lObjBBApp_cjbBBAppResult;
	}
}
